﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using VswService_V7.Utility;
using VswService_V7.DataBase.Tables.L2INTERFACE;

namespace VswService_V7.DataBase.Tables
{
    public partial class DOWNTIME_TABLE
    {
        #region  field
        public static readonly string tableName = "DOWNTIME_TABLE";
        public static readonly string fd_eventNo = "EVENT_NO";
        public static readonly string fd_downtimeNo = "DOWNTIME_ID";
        public static readonly string fd_stopType = "STOP_TYPE";
        public static readonly string fd_machineNo = "MACHINE_ID";
        public static readonly string fd_startTime = "START_TIME";
        public static readonly string fd_endTime = "END_TIME";
        public static readonly string fd_serviceTeam = "SERVICE_TEAM";
        public static readonly string fd_stopReason = "STOP_REASON";
        public static readonly string fd_subReason = "SUB_REASON";
        public static readonly string fd_status = "STATUS";
        public static readonly string fd_downComment = "DOWN_COMMENT";
        public static readonly string fd_operateName = "OPERATE_NAME";
        public static readonly string fd_shiftName = "SHIFT_NAME";
        public static readonly string fd_updateTime = "UPDATE_TIME";
        #endregion


        /// <summary>
        /// a new downtime record is automatically generated,the fault signal source is L1,and status=1 is maked
        /// </summary>
        /// <param name="downTime"></param>
        /// <param name="downTimeStatus"></param>
        /// <returns>eventNo</returns>
        public static long CreateNewDownTimeRecord(DateTime downTime, string downTimeStatus)
        {
            long eventNo = OracleHelper.GetNextEventNo();
            string downtimeId = downTime.ToString(Formats.DowntimeIdFormat);
            try
            {
                List<string> sqlList = new List<string>();
                string stopType = downTimeStatus.Substring(0, 1);
                string machineNo = downTimeStatus.Substring(1);
                string sql = string.Format("insert into {0}" +
                    "({1}," +
                    "{2},{3},{4},{5},{6}," +
                    "{7},{8},{9},{10}," +
                    "{11},{12},{13})" +
                    "values({14}," +
                    "'{15}','{16}','{17}','{18}','{19}'," +
                    "'{20}','{21}','{22}','{23}'," +
                    "'{24}','{25}','{26}')",
                    tableName,
                    fd_eventNo,
                    fd_downtimeNo, fd_stopType, fd_machineNo, fd_startTime, fd_endTime,
                    fd_serviceTeam, fd_stopReason, fd_status, fd_downComment,
                    fd_operateName, fd_shiftName, fd_updateTime,
                    eventNo,
                    downtimeId, stopType, machineNo, downTime.ToString(Formats.GetDateFormat()), "0",
                    "0", "0", "0", "0",
                    "Auto", "DAY_SHIFT", downTime.ToString(Formats.GetDateFormat()));


                string sqlINTFDowntime = string.Format("insert into {0}" +
                    "({1}," +
                    "{2},{3},{4},{5},{6}," +
                    "{7},{8},{9},{10})" +
                    "values({11}," +
                    "'{12}','{13}','{14}','{15}','{16}'," +
                    "'{17}','{18}','{19}','{20}')",
                    L2INTF_DOWNTIME.tableName,
                    L2INTF_DOWNTIME.fd_eventNo,
                    L2INTF_DOWNTIME.fd_downtimeNo, L2INTF_DOWNTIME.fd_stopType, L2INTF_DOWNTIME.fd_machineNo, L2INTF_DOWNTIME.fd_startTime, L2INTF_DOWNTIME.fd_endTime,
                    L2INTF_DOWNTIME.fd_serviceTeam, L2INTF_DOWNTIME.fd_stopReason, L2INTF_DOWNTIME.fd_status, L2INTF_DOWNTIME.fd_downComment,
                    eventNo,
                    downtimeId, stopType, machineNo, downTime.ToString(Formats.GetDateFormat()), "0",
                    "0", "0", "0", "0");


                string sqlINTFEVENT = string.Format("insert into {0} values({1},'{2}','{3}','{4}','{5}','{6}')",
                    L2INTF_EVENT.tableName, eventNo, L2INTF_EVENT.generateOrUpdateDowntime, DateTime.Now.ToString(Formats.GetDateFormat()), "1", "L1", "");

                sqlList.Add(sql);
                sqlList.Add(sqlINTFDowntime);
                sqlList.Add(sqlINTFEVENT);
                OracleHelper.ExecuteSqlTran(sqlList);
                return eventNo;
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("CreateNewDownTimeRecord", ex);
                return -1;
            }
        }

        /// <summary>
        /// automatically update all the records of downtime status=0,the fault signal source is L1,and status=1 is maked
        /// </summary>
        /// <param name="endTime"></param>
        /// <returns>eventNo</returns>
        public static long UpdateDownTimeRecord(DateTime endTime)
        {
            try
            {

                DataTable downTimeTable = OracleHelper.Exct_QueryTable(string.Format("select * from {0} where {1}='{2}'", tableName, fd_status, "0"));

                if(downTimeTable!=null && downTimeTable.Rows.Count>0)
                {
                    long eventNo = OracleHelper.GetNextEventNo();
                    string sql = string.Format("update {0} " +
                            "set {1}={2}," +
                            "{3}='{4}'," +
                            "{5}='{6}' " +
                            "where {7}='{8}'",
                            tableName,
                            fd_eventNo, eventNo,
                            fd_endTime, endTime.ToString(Formats.GetDateFormat()),
                            fd_status, "1",
                            fd_status, "0");
                    OracleHelper.Exct_NonQuery(sql);


                    List<string> sqlList = new List<string>();
                    for (int i = 0; i < downTimeTable.Rows.Count; i++)
                    {
                        string sqlINTFDowntime = string.Format("insert into {0}" +
                    "({1}," +
                    "{2},{3},{4},{5},{6}," +
                    "{7},{8},{9},{10})" +
                    "values({11}," +
                    "'{12}','{13}','{14}','{15}','{16}'," +
                    "'{17}','{18}','{19}','{20}')",
                   L2INTF_DOWNTIME.tableName,
                   L2INTF_DOWNTIME.fd_eventNo,
                   L2INTF_DOWNTIME.fd_downtimeNo, L2INTF_DOWNTIME.fd_stopType, L2INTF_DOWNTIME.fd_machineNo, L2INTF_DOWNTIME.fd_startTime, L2INTF_DOWNTIME.fd_endTime,
                   L2INTF_DOWNTIME.fd_serviceTeam, L2INTF_DOWNTIME.fd_stopReason, L2INTF_DOWNTIME.fd_status, L2INTF_DOWNTIME.fd_downComment,
                    eventNo,
                    downTimeTable.Rows[i][fd_downtimeNo].ToString(), downTimeTable.Rows[i][fd_stopType].ToString(), downTimeTable.Rows[i][fd_machineNo].ToString(), downTimeTable.Rows[i][fd_startTime].ToString(), endTime.ToString(Formats.GetDateFormat()),
                    "0", "0", "1", "0");
                        sqlList.Add(sqlINTFDowntime);
                    }

                    string sqlINTFEVENT = string.Format("insert into {0} values({1},'{2}','{3}','{4}','{5}','{6}')",
                    L2INTF_EVENT.tableName, eventNo, L2INTF_EVENT.generateOrUpdateDowntime, DateTime.Now.ToString(Formats.GetDateFormat()), "1", "L1", "");
                    sqlList.Add(sqlINTFEVENT);
                    OracleHelper.ExecuteSqlTran(sqlList);
                    return eventNo;
                }

                else
                {
                    return -1;
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("UpdateDownTimeRecord", ex);
                return -1;
            }

        }

    }





}
